<?php
namespace app\controllers\crontab;
use Yii;
use app\components\Site;
use app\models\Elog;
use app\components\redis\RedisMarketingEmail;

class DealChannelStatisticsDataController extends \app\base\Controller 
{
    /**
     * @var RedisMarketingEmail
     */
    private $redis;
    /**
     * 初始化
     */
    public function init()
    {
        $this->redis = new RedisMarketingEmail();
//        yii::$app->db->enableSlaves = false;        // 禁用从库查询，从主库查询数据。
    }
    
    /**
     * 更改队列信息的默认状态
     */
    public function actionUpdateListSendEmarsysStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=1 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and (channel_id=2 or channel_id=6)"; // 把剩余的数据的发送状态置为成功
            $this->writeLog("UpdateListSendEmarsysStatus:".$updateSql);
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    /**
     * yumao
     * 2018-5-9
     * benchmark更改队列信息的默认状态
     */
    public function actionUpdateListSendBenchmarkStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=1 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and channel_id=5"; // 把剩余的数据的发送状态置为成功
            $this->writeLog("UpdateListSendEmarsysStatus:".$updateSql);
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    /**
     * 更改队列信息的默认状态
     */
    public function actionUpdateListSendCheetStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=2 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and channel_id=4"; // 把剩余的数据的发送状态置为失败
            $this->writeLog("UpdateListSendCheetStatus:".$updateSql);
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    /**
    *     @todo: 更新队列信息的默认状态
    *     @date: 2018年5月21日 下午5:10:09
    *     @param: variable
    *     @return:
    */
    public function actionUpdateListSendWebpowerStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=1 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and channel_id=9"; // 把剩余的数据的发送状态置为失败
            $this->writeLog("UpdateListSendWebpowerStatus:".$updateSql);
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    
    /**
     * 把channel_return_info_statistics表中的数据统计到
     * mail_group_email_type_statistics中
     */ 
    public function actionDataToTypeStatistics()
    {
        // 每个gid和email_type只有一条数据
        $tableName = "channel_return_info_statistics";
        $sql = "select * from {$tableName} where is_deal=0";
        $allInfo = Yii::$app->db->createCommand($sql)->queryAll();
        $curBjTime = \yii::$app->bjTime->time();
        $idArr = [];
        $idStr = "";
        foreach ($allInfo as $allInfoV) {
            $idArr[] = $allInfoV['id'];
            $selectSql = "select count(*) from mail_group_email_type_statistics where gid={$allInfoV['gid']} and email_type={$allInfoV['email_type']}";
            $gidType = Yii::$app->db->createCommand($selectSql)->queryScalar();
            $sendFail = $allInfoV['hard_bounces_nums'] + $allInfoV['soft_bounces_nums'];
            if ($gidType) {
                
                // 更新数据
                $updateSql = "update mail_group_email_type_statistics set send_success=send_success+{$allInfoV['send_success_nums']},send_fail=send_fail+{$sendFail},click_nums=click_nums+{$allInfoV['click_nums']},update_time={$curBjTime} where  gid={$allInfoV['gid']} and email_type={$allInfoV['email_type']}";
                Yii::$app->db->createCommand($updateSql)->execute();
            } else {
                
                // 插入数据
                $insertSql = "insert into mail_group_email_type_statistics(gid,email_type,send_success,send_fail,click_nums,update_time) values ({$allInfoV['gid']},{$allInfoV['email_type']},{$allInfoV['send_success_nums']},{$sendFail},{$allInfoV['click_nums']},{$curBjTime})";
                Yii::$app->db->createCommand($insertSql)->execute();
            }
            
            // 更新分组表中的数据
            if ($allInfoV['gid']) {
                $updateGroupSql = "update mail_group set send_status_ok_count=send_status_ok_count+{$allInfoV['send_success_nums']},send_status_fail_count=send_status_fail_count+{$sendFail},click_link_count=click_link_count+{$allInfoV['click_nums']} where group_id = {$allInfoV['gid']}";
                Yii::$app->db->createCommand($updateGroupSql)->execute();
            }
            
        }
        if ($idArr) {
            $idStr = join(',',$idArr);
            $updateSqlDeal = "update {$tableName} set is_deal = 1 where id in({$idStr})";
            Yii::$app->db->createCommand($updateSqlDeal)->execute();
        }
        
       
        echo '处理完毕';
    }
    
    /**
     * emarsys数据统计
     */
    public function actionStatisticsEmarsysData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $step = Yii::$app->request->get('step') ? Yii::$app->request->get('step') : 1;
        if($step == 1){
            $this->statisticsData();
        }
        if($step == 2)
        {
            $this->statisticsLocalData();
        }
    }
    
    /**
     * cheet数据统计
     */
    public function actionStatisticsCheetData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $step = Yii::$app->request->get('step') ? Yii::$app->request->get('step') : 1;
        if($step == 1){
            $this->statisticsData(4);
        }
        if($step == 2)
        {
            $this->statisticsLocalData(4);
        }
        
    }
   
    /**
     * 统计本地发送总数和失败数量
     */
    public function statisticsLocalData($channelIdZdy=0) 
    {
        
        if (empty($channelIdZdy)) {
            $channelWhere = ' (mg.channel_id = 2 or mg.channel_id = 6)';
             $redirectBaseUrl = "crontab/deal-channel-statistics-data/statistics-emarsys-data?site=marketing_email";
            $cahnnelName = 'emarsys';
        } else {
            $channelWhere = " mg.channel_id = {$channelIdZdy} ";
            $redirectBaseUrl = "crontab/deal-channel-statistics-data/statistics-cheet-data?site=marketing_email";
            $cahnnelName = 'cheet';
        }
//        $getGroupId = Yii::$app->request->get('group_id') ? Yii::$app->request->get('group_id') : 0;
//        if($getGroupId) {
//            $channelWhere .= " and mg.group_id = {$getGroupId} ";
//        }
        
        // 获取前一天的数据
        $curTime = \yii::$app->bjTime->time();
        
//        $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/' ;

        // 遍历所有的发送列表
        $sql = "select mg.*,mt.type_id from mail_group as mg left join  mail_template as mt on mg.template_pk = mt.id  where mg.state = 2 and is_stat_open_rate=0 and $channelWhere limit 1";
       
        $groupInfo = yii::$app->db->createCommand($sql)->queryAll();
        $numbers = 3000; //每次处理的数据量
        if ($groupInfo) {
            foreach ($groupInfo as $groupInfoV) {
                
                $siteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoV['site_id']];
                
                $tableName = "mail_sendlist_$siteName";
                
                for($i=0;$i<100;$i++) {
                    
                    // 查询相关数据
                    $totalSql = " select mg.id,mg.email,mg.state,mg.email_type,mg.last_send from $tableName as mg where mg.group_id = {$groupInfoV['group_id']} and mg.statistics_status = 0 and  (mg.state = 2 or mg.state=3) limit $numbers";

                    $emailArr = yii::$app->db->createCommand($totalSql)->queryAll();

                    // 保存相关数据
                    if ($emailArr) {
                        $groupId =  $groupInfoV['group_id'];
                        $groupName = $groupInfoV['group_name'];
                        $this->writeLog($cahnnelName."通道statisticsLocalData数据处理分组：{$groupInfoV['group_id']}第{$i}次！");
                        $templateTypeId = $groupInfoV['type_id'];
                        $channelId = $groupInfoV['channel_id'];
                        $siteId = $groupInfoV['site_id'];
                        $emailArrDealAll = [];  // 总数
                        $emailArrDealFail = []; // 发送失败的数量
                        $emailArrIds = []; // 发送失败的数量
                        foreach ($emailArr as $emailArrV) {
                            $emailArrIds[] = $emailArrV['id'];
                            $statDate = \yii::$app->bjTime->date("Y-m-d",$emailArrV['last_send']);
                            if ($emailArrV['state'] == 3) {
                                if (isset($emailArrDealFail[$emailArrV['email_type']][$statDate])) {
                                    $emailArrDealFail[$emailArrV['email_type']][$statDate]++;
                                } else {
                                    $emailArrDealFail[$emailArrV['email_type']][$statDate] = 1;
                                }
                            }
                            if (isset($emailArrDealAll[$emailArrV['email_type']][$statDate])) {
                                $emailArrDealAll[$emailArrV['email_type']][$statDate]++;
                            } else {
                                $emailArrDealAll[$emailArrV['email_type']][$statDate] = 1;
                            }

                        } 

                        /*** 往统计表中添加或者更新数据 start ****/
                        foreach ($emailArrDealAll as $emailArrDealAllK => $emailArrDealAllV) {

                            foreach($emailArrDealAllV as $statDate => $emailArrDealAllNum) {
                                $selectSql = "select count(*) from channel_return_info_statistics where gid={$groupId} and stat_date='{$statDate}' and email_type={$emailArrDealAllK}";
                                $gidDateType = Yii::$app->db->createCommand($selectSql)->queryScalar();

                                if ($gidDateType) {
                                    if (isset($emailArrDealFail[$emailArrDealAllK][$statDate]) && $emailArrDealFail[$emailArrDealAllK][$statDate]) {
                                        $updateSql = "update channel_return_info_statistics set send_total_nums = send_total_nums + {$emailArrDealAllNum},send_fail_nums= send_fail_nums+{$emailArrDealFail[$emailArrDealAllK][$statDate]},stat_time={$curTime} where gid={$groupId} and stat_date='{$statDate}' and email_type={$emailArrDealAllK}";
                                    } else {
                                        $updateSql = "update channel_return_info_statistics set send_total_nums = send_total_nums + {$emailArrDealAllNum},stat_time={$curTime} where gid={$groupId} and stat_date='{$statDate}' and email_type={$emailArrDealAllK}";
                                    }
                                    Yii::$app->db->createCommand($updateSql)->execute();
                                } else {                            
                                    if (isset($emailArrDealFail[$emailArrDealAllK][$statDate]) && $emailArrDealFail[$emailArrDealAllK][$statDate]) {
                                        $insertSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,send_total_nums,send_fail_nums,stat_date,stat_time) values ({$channelId},{$siteId},{$groupId},'{$groupName}',{$emailArrDealAllK},{$templateTypeId},{$emailArrDealAllNum},{$emailArrDealFail[$emailArrDealAllK][$statDate]},'{$statDate}',{$curTime})";
                                    } else {
                                        $insertSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,send_total_nums,stat_date,stat_time) values ({$channelId},{$siteId},{$groupId},'{$groupName}',{$emailArrDealAllK},{$templateTypeId},{$emailArrDealAllNum},'{$statDate}',{$curTime})";
                                    }
                                    Yii::$app->db->createCommand($insertSql)->execute();
                                }
                            }


                        }

                        /*** 往统计表中添加或者更新数据 end ****/

                    } else {
                        //没有数据说明该分组已统计完成，将其状态更改

                        $updateDealState = "update mail_group set is_stat_open_rate = 1 where group_id = {$groupInfoV['group_id']}";
                        Yii::$app->db->createCommand($updateDealState)->execute();
                        $this->writeLog($cahnnelName."通道statisticsLocalData数据处理分组：{$groupInfoV['group_id']}完成！");
                        echo $cahnnelName."通道statisticsLocalData数据处理分组：{$groupInfoV['group_id']}完成！";
                        break;
                    }
                    if($emailArrIds) {
                        $emailIds = join(',',$emailArrIds);
                        $updateDealState = "update $tableName set statistics_status = 1 where id in ({$emailIds})";
                        Yii::$app->db->createCommand($updateDealState)->execute();
                    }
                }
            }
            
            
            
        } else {
            $this->writeLog($cahnnelName."通道statisticsLocalData数据全部处理完成！");
            echo $cahnnelName."通道statisticsLocalData数据全部处理完成！";
        }
        
        return $groupInfo;
    }
    
    
    
    /*
     * 从明细表统计数量到统计表中
     * @liugang 2017-12-07
     * 
     */
    public function actionStatisticsData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $channelType = Yii::$app->request->get('channelType') ? Yii::$app->request->get('channelType') : 1;//1是emarsys和emarsys_new通道，2是cheet通道
        
        if ($channelType == 1) {
            $channelWhere = ' channel_id in(2,6) ';
            $channelName = 'emarsys';
        } else if($channelType == 2) {
            $channelWhere = " channel_id = 4 ";
            $channelName = "cheet";
        } elseif ($channelType == 5) {
            $channelWhere = " channel_id = 5 ";
            $channelName = "benchmark";
        } elseif ($channelType == 9) {
            $channelWhere = " channel_id = 9 ";
            $channelName = "webpower";
        }
        
        $tableName = "mail_trace_detail";
        
        //从明细表查询未被统计的分组
        $sql = "select gid from {$tableName} where is_deal=0 and {$channelWhere} group by gid";
        $allGidArr = Yii::$app->db->createCommand($sql)->queryAll();
        $gidArr = $groupInfoArrDeal = $siteIdsArr = array();
//        var_dump($allGidArr);exit;
        if($allGidArr)
        {
            foreach($allGidArr as $gid) {
                $gidArr[] = $gid['gid'];
            }
        }
        if ($gidArr) {
            
            try{
                    $curBjTime = \yii::$app->bjTime->time();
                    $gidStr = join(',',$gidArr);
                    // 从分组表中查询分组名称和网站id的相关数据
                    $sqlGroup = "select mg.group_id,mg.channel_id,mg.group_name,mg.site_id,mt.type_id from mail_group as mg left join mail_template as mt on mg.template_pk=mt.id where group_id in ({$gidStr})";
                    $groupInfoArr = Yii::$app->db->createCommand($sqlGroup)->queryAll();
                    if ($groupInfoArr) {
                        foreach ($groupInfoArr as $groupInfoArrV) {
                            $groupInfoArrDeal[$groupInfoArrV['group_id']] = $groupInfoArrV;

                            $siteIdsArr[$groupInfoArrV['site_id']][] = $groupInfoArrV['group_id'];
                            //数据的初始化
                            $curGroupTotalSuccessNums[$groupInfoArrV['group_id']] = $curGroupTotalBouncesNums[$groupInfoArrV['group_id']] = $curGroupTotalClicksNums[$groupInfoArrV['group_id']] = 0;
                            for( $i=0 ; $i < 6 ; $i++ ) {
                                $curGroupPerEmailTypeSuccessNums[$groupInfoArrV['group_id']][$i] = 0;
                                $curGroupPerEmailTypeBouncesNums[$groupInfoArrV['group_id']][$i] = 0;
                                $curGroupPerEmailTypeClicksNums[$groupInfoArrV['group_id']][$i]  = 0;
                            }

                        }
                    }

                    $insertNumsValueArr = array();
                    //统计当前分组的通道返回发送成功数量
                    $getSuccessNumsSql = "select count(*) num,gid,email_type,send_success_date stat_date from {$tableName} where is_send_success = 1 and gid in ({$gidStr}) group by gid,email_type,send_success_date";

                    $groupSuccessInfoArr = Yii::$app->db->createCommand($getSuccessNumsSql)->queryAll();

                    foreach($groupSuccessInfoArr as $groupSuccessInfo) {
                        $insertNumsValueArr[$groupSuccessInfo['gid']][$groupSuccessInfo['email_type']][$groupSuccessInfo['stat_date']]['successNums'] = $groupSuccessInfo['num'];
                    }
                    //统计当前分组的通道返回发送成功数量结束
                    //统计当前分组的通道返回发送失败（硬弹）数量
                    $getHardBouncesNumsSql = "select count(*) num,gid,email_type,bounces_date stat_date from {$tableName} where is_hard_bounces = 1 and gid in ({$gidStr}) group by gid,email_type,bounces_date";

                    $groupHardBouncesInfoArr = Yii::$app->db->createCommand($getHardBouncesNumsSql)->queryAll();

                    foreach($groupHardBouncesInfoArr as $groupHardBouncesInfo) {
                        $insertNumsValueArr[$groupHardBouncesInfo['gid']][$groupHardBouncesInfo['email_type']][$groupHardBouncesInfo['stat_date']]['hardBouncesNums'] = $groupHardBouncesInfo['num'];
                    }
                    //统计当前分组的通道返回发送失败（硬弹）数量结束
                    //统计当前分组的通道返回发送失败（软弹）数量
                    $getSoftBouncesNumsSql = "select count(*) num,gid,email_type,bounces_date stat_date from {$tableName} where is_soft_bounces = 1 and gid in ({$gidStr}) group by gid,email_type,bounces_date";

                    $groupSoftBouncesInfoArr = Yii::$app->db->createCommand($getSoftBouncesNumsSql)->queryAll();

                    foreach($groupSoftBouncesInfoArr as $groupSoftBouncesInfo) {
                        $insertNumsValueArr[$groupSoftBouncesInfo['gid']][$groupSoftBouncesInfo['email_type']][$groupSoftBouncesInfo['stat_date']]['softBouncesNums'] = $groupSoftBouncesInfo['num'];
                    }
                    //统计当前分组的通道返回发送失败（软弹）数量结束
                    //统计当前分组的通道返回投诉数量
                    $getComplaintNumsSql = "select count(*) num,gid,email_type,complaint_date stat_date from {$tableName} where is_complaint = 1 and gid in ({$gidStr}) group by gid,email_type,complaint_date";

                    $groupComplaintInfoArr = Yii::$app->db->createCommand($getComplaintNumsSql)->queryAll();

                    foreach($groupComplaintInfoArr as $groupComplaintInfo) {
                        $insertNumsValueArr[$groupComplaintInfo['gid']][$groupComplaintInfo['email_type']][$groupComplaintInfo['stat_date']]['complaintNums'] = $groupComplaintInfo['num'];
                    }
                    //统计当前分组的通道返回投诉数量结束
                    //统计当前分组的通道返回退订数量
                    $getUnsubscribeNumsSql = "select count(*) num,gid,email_type,unsubscribe_date stat_date from {$tableName} where is_unsubscribe = 1 and gid in ({$gidStr}) group by gid,email_type,unsubscribe_date";

                    $groupUnsubscribeInfoArr = Yii::$app->db->createCommand($getUnsubscribeNumsSql)->queryAll();
            
                    foreach($groupUnsubscribeInfoArr as $groupUnsubscribeInfo) {
                        $insertNumsValueArr[$groupUnsubscribeInfo['gid']][$groupUnsubscribeInfo['email_type']][$groupUnsubscribeInfo['stat_date']]['unsubscribeNums'] = $groupUnsubscribeInfo['num'];
                    }
                    //统计当前分组的通道返回退订数量结束
                    //统计当前分组的通道返回点击数量
                    $getClickNumsSql = "select count(*) num,gid,email_type,click_date stat_date from {$tableName} where is_click = 1 and gid in ({$gidStr}) group by gid,email_type,click_date";

                    $groupClickInfoArr = Yii::$app->db->createCommand($getClickNumsSql)->queryAll();

                    foreach($groupClickInfoArr as $groupClickInfo) {
                        $insertNumsValueArr[$groupClickInfo['gid']][$groupClickInfo['email_type']][$groupClickInfo['stat_date']]['clickNums'] = $groupClickInfo['num'];
                    }
                    //统计当前分组的通道返回点击数量结束

                    if($insertNumsValueArr) {
                        //拼接插入统计表channel_return_info_statistics的sql
                        $insertNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,send_success_nums,hard_bounces_nums,soft_bounces_nums,complaint_nums,unsubscribe_nums,click_nums,stat_date,stat_time) values ";
                        $insertValueArr = array();
                        foreach($insertNumsValueArr as $gid => $insertNumsValues) {
                            //没有当前分组信息就跳过
                            if(isset($groupInfoArrDeal[$gid])) {

                                foreach($insertNumsValues as $emailType => $insertNumsValue) {

                                    foreach($insertNumsValue as $statDate => $insertNumsVal) {
                                
                                        $sendSuccessNums = isset($insertNumsVal['successNums']) ? $insertNumsVal['successNums'] : 0;
                                        $hardBouncesNums = isset($insertNumsVal['hardBouncesNums']) ? $insertNumsVal['hardBouncesNums'] : 0;
                                        $softBouncesNums = isset($insertNumsVal['softBouncesNums']) ? $insertNumsVal['softBouncesNums'] : 0;
                                        $complaintNums = isset($insertNumsVal['complaintNums']) ? $insertNumsVal['complaintNums'] : 0;
                                        $unsubscribeNums = isset($insertNumsVal['unsubscribeNums']) ? $insertNumsVal['unsubscribeNums'] : 0;
                                        $clickNums = isset($insertNumsVal['clickNums']) ? $insertNumsVal['clickNums'] : 0;
                                        $insertValueArr[] = "({$groupInfoArrDeal[$gid]['channel_id']},{$groupInfoArrDeal[$gid]['site_id']},{$gid},'{$groupInfoArrDeal[$gid]['group_name']}',{$emailType},{$groupInfoArrDeal[$gid]['type_id']},{$sendSuccessNums},{$hardBouncesNums},{$softBouncesNums},{$complaintNums},{$unsubscribeNums},{$clickNums},'{$statDate}',{$curBjTime})";

                                        $curGroupPerEmailTypeSuccessNums[$gid][$emailType] += $sendSuccessNums;
                                        $curGroupPerEmailTypeBouncesNums[$gid][$emailType] += $hardBouncesNums;
                                        $curGroupPerEmailTypeBouncesNums[$gid][$emailType] += $softBouncesNums;
                                        $curGroupPerEmailTypeClicksNums[$gid][$emailType] += $clickNums;

                                        $curGroupTotalSuccessNums[$gid] += $sendSuccessNums;
                                        $curGroupTotalBouncesNums[$gid] += $hardBouncesNums;
                                        $curGroupTotalBouncesNums[$gid] += $softBouncesNums;
                                        $curGroupTotalClicksNums[$gid] += $clickNums;
                                    }

                                }
                            }

                        }
                        //执行写入数据库
                        if($insertValueArr) {
                            $insertNumsSql = $insertNumsSql.join(',',$insertValueArr)." ON DUPLICATE KEY UPDATE send_success_nums=values(send_success_nums),hard_bounces_nums=values(hard_bounces_nums),soft_bounces_nums=values(soft_bounces_nums),complaint_nums=values(complaint_nums),unsubscribe_nums=values(unsubscribe_nums),click_nums=values(click_nums),stat_time={$curBjTime}";

                            // 入库
                            Yii::$app->db->createCommand($insertNumsSql)->execute();
                        }
                        //拼接插入统计表channel_return_info_statistics的sql结束
                        //拼接插入统计表mail_group_email_type_statistics的sql
                        $insertSql = "insert into mail_group_email_type_statistics(gid,email_type,send_success,send_fail,click_nums,update_time) values ";
                        $insertStatisticsValuesArr = array();
                        foreach($curGroupPerEmailTypeSuccessNums as $gid => $curGroupPerEmailTypeSuccessNum) {
                            foreach($curGroupPerEmailTypeSuccessNum as $emailType => $successNums) {
                                if($successNums || $curGroupPerEmailTypeBouncesNums[$gid][$emailType] || $curGroupPerEmailTypeClicksNums[$gid][$emailType]) {
                                    $bouncesNums = $curGroupPerEmailTypeBouncesNums[$gid][$emailType];
                                    $clickNums = $curGroupPerEmailTypeClicksNums[$gid][$emailType];
                                    $insertStatisticsValuesArr[] = "({$gid},{$emailType},{$successNums},{$bouncesNums},{$clickNums},{$curBjTime})";
                                }
                            }
                        }
                        if($insertStatisticsValuesArr) {
                            $insertSql = $insertSql.join(',',$insertStatisticsValuesArr)." ON DUPLICATE KEY UPDATE send_success=values(send_success),send_fail=values(send_fail),click_nums=values(click_nums),update_time={$curBjTime}";
                            // 入库
                            Yii::$app->db->createCommand($insertSql)->execute();
                         }

                        //拼接插入统计表mail_group_email_type_statistics的sql结束
                        //统计当前分组通道明细数据到mail_group表中

                        $updateGroupSql = "update mail_group set ";
                        $updateGroupSunccessArr = $updateGroupFailArr = $updateGroupClickArr = $updateGroupArr = array();

                        foreach($curGroupTotalSuccessNums as $groupId => $totalSuccessNums) {
                            if($totalSuccessNums || $curGroupTotalBouncesNums[$groupId] || $curGroupTotalClicksNums[$groupId]) {
                                $updateGroupSunccessArr[] = sprintf("WHEN %d THEN %d ", $groupId, $totalSuccessNums);
                                $updateGroupFailArr[] = sprintf("WHEN %d THEN %d ", $groupId, $curGroupTotalBouncesNums[$groupId]);
                                $updateGroupClickArr[] = sprintf("WHEN %d THEN %d ", $groupId, $curGroupTotalClicksNums[$groupId]);
                            }
                        }
                        if($updateGroupSunccessArr) {
                            $updateGroupArr[] = "send_status_ok_count = CASE group_id ".join('',$updateGroupSunccessArr)." END";
                        } 
                        if($updateGroupFailArr) {
                            $updateGroupArr[] = "send_status_fail_count = CASE group_id ".join('',$updateGroupFailArr)." END";
                        }
                        if($updateGroupClickArr) {
                            $updateGroupArr[] = "click_link_count = CASE group_id ".join('',$updateGroupClickArr)." END";
                        }

                        if($updateGroupArr) {
                            $updateGroupSql .= join(',',$updateGroupArr)." where group_id in ($gidStr)";
//                            var_dump($updateGroupSql);exit;
                            // 入库
                            Yii::$app->db->createCommand($updateGroupSql)->execute();
                        }

                        
                        

                        //统计当前分组通道明细数据到mail_group表中结束
                        
                        if($siteIdsArr) {
                            
                            foreach($siteIdsArr as $siteId => $groupIds) {
                                if($groupIds) {
                                    $groupIdsStr = join(',',$groupIds);
                                    
                                    $insertTempSql = "insert into mail_statistics_temp(site_id,group_id,channel_id,email,is_hard_bounces,is_soft_bounces,is_complaint,is_send_success,is_click,click_time) (select {$siteId},gid,channel_id,email,is_hard_bounces,is_soft_bounces,is_complaint,is_send_success,is_click,click_time from {$tableName} where is_deal = 0 and gid in({$groupIdsStr}) and (is_hard_bounces=1 or is_soft_bounces=1 or is_complaint=1 or is_send_success=1 or is_click=1))";
                                    Yii::$app->db->createCommand($insertTempSql)->execute();    
                                    
                                    // 将通道返回的明细表中退订数据写入到mongodb中
                                    $getUnsubscribesSql = "select gid,email,unsubscribe_time from {$tableName} where is_deal = 0 and gid in({$groupIdsStr}) and is_unsubscribe = 1";
                                    
                                    $getUnsubscribesDatas = Yii::$app->db->createCommand($getUnsubscribesSql)->queryAll();
                                    
                                    // 获取网站名称
                                    $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);
                                    
                                    foreach($getUnsubscribesDatas as $getUnsubscribesData) 
                                    {

                                        // 组装写入MongoDB的数据,更新数据的条件
                                        $updateCondition = [
                                            'group_id' => $getUnsubscribesData['gid'],
                                            'email'  => $getUnsubscribesData['email']
                                        ];

                                        // 需要更新的数据
                                        $updateData = [
                                            '$set' => [
                                                'unsubscribe_time' => \yii::$app->bjTime->strtotime($getUnsubscribesData['unsubscribe_time']),
                                                'is_sync'  => 0
                                            ],
                                            '$setOnInsert' => [
                                                'unsubscribe_source'   => 3, // 退订来源：用户邮件
                                            ]
                                        ];

                                        // 如果存在group_id和email的记录就更新,否则就插入
                                        try {
                                            Yii::$app->mongodb->getCollection('mail_group_unsubscribe_'.$siteShortName)->update($updateCondition,$updateData,array('upsert' => true));
                                        } catch (\Exception $e) {
                                            $msg = $e->getMessage().'! params: siteId:'.$siteId.';groupId:'.$getUnsubscribesData['gid'].';email:'.$getUnsubscribesData['email'].';unsubscribe_time:'.\yii::$app->bjTime->strtotime($getUnsubscribesData['unsubscribe_time']);
                                            Elog::logCrontab($msg, 'actionStatisticsData','exception');

                                        }
                                    }
                                    
                                    
                                }
                            }
                            
                        }
                        

                    }

                    //分组处理完后更改其状态
                    $updateTraceSql = "update {$tableName} set is_deal = 1 where gid in({$gidStr}) and is_deal = 0";
                    Yii::$app->db->createCommand($updateTraceSql)->execute();
                    
            } catch (\Exception $e) {
                
                    echo "{$channelName}通道数据统计失败：".$e->getMessage();
                    $this->writeLog("{$channelName}通道数据统计失败：".$e->getMessage());
            }
            
            echo "{$channelName}通道数据统计完成！";
            $this->writeLog("{$channelName}通道数据统计完成！");
        } else {
            echo "{$channelName}通道没有要处理的数据！";
            $this->writeLog("{$channelName}通道没有要处理的数据！");
        }        
    }
    
    /*
     * 手动将通道返回的退订明细数据写入到mongodb中
     */
    public function actionSynUnsubscribeToMongodb() 
    {
        
        $tableName = "mail_trace_detail";
        
        //从明细表查询未被统计的分组
        $sql = "select gid from {$tableName} where is_deal=1 group by gid";
        $allGidArr = Yii::$app->db->createCommand($sql)->queryAll();
        $gidArr =  $siteIdsArr = array();
//        var_dump($allGidArr);exit;
        if($allGidArr)
        {
            foreach($allGidArr as $gid) {
                $gidArr[] = $gid['gid'];
            }
        }
        $gidStr = join(',',$gidArr);
        // 从分组表中查询分组名称和网站id的相关数据
        $sqlGroup = "select group_id,site_id from mail_group where group_id in ({$gidStr})";
        $groupInfoArr = Yii::$app->db->createCommand($sqlGroup)->queryAll();
        if ($groupInfoArr) {
            foreach ($groupInfoArr as $groupInfoArrV) {
                $siteIdsArr[$groupInfoArrV['site_id']][] = $groupInfoArrV['group_id'];
                
            }
        }
        
        //统计当前分组通道明细数据到mail_group表中结束
                        
        if($siteIdsArr) {

            foreach($siteIdsArr as $siteId => $groupIds) {
                if($groupIds) {
                    $groupIdsStr = join(',',$groupIds);

                    // 将通道返回的明细表中退订数据写入到mongodb中
                    $getUnsubscribesSql = "select gid,email,unsubscribe_time from {$tableName} where is_deal = 1 and gid in({$groupIdsStr}) and is_unsubscribe = 1";

                    $getUnsubscribesDatas = Yii::$app->db->createCommand($getUnsubscribesSql)->queryAll();

                    // 获取网站名称
                    $siteShortName = Site::getInstance()->getSiteShortNameById($siteId);

                    foreach($getUnsubscribesDatas as $getUnsubscribesData) 
                    {

                        // 组装写入MongoDB的数据,更新数据的条件
                        $updateCondition = [
                            'group_id' => $getUnsubscribesData['gid'],
                            'email'  => $getUnsubscribesData['email']
                        ];

                        // 需要更新的数据
                        $updateData = [
                            '$set' => [
                                'unsubscribe_time' => \yii::$app->bjTime->strtotime($getUnsubscribesData['unsubscribe_time']),
                                'is_sync'  => 0
                            ],
                            '$setOnInsert' => [
                                'unsubscribe_source'   => 3, // 退订来源：用户邮件
                            ]
                        ];

                        // 如果存在group_id和email的记录就更新,否则就插入
                        try {
                            Yii::$app->mongodb->getCollection('mail_group_unsubscribe_'.$siteShortName)->update($updateCondition,$updateData,array('upsert' => true));
                        } catch (\Exception $e) {
                            $msg = $e->getMessage().'! params: siteId:'.$siteId.';groupId:'.$getUnsubscribesData['gid'].';email:'.$getUnsubscribesData['email'].';unsubscribe_time:'.\yii::$app->bjTime->strtotime($getUnsubscribesData['unsubscribe_time']);
                            Elog::logCrontab($msg, 'actionStatisticsData','exception');

                        }
                    }


                }
            }

        }
        
        echo '数据处理完成！';
        
    }
    /*
     *定时同步统计临时表数据到正式表中 
     */
    public function actionSynStatisticsTemp() {
        yii::$app->db->enableSlaves = false;        // 禁用从库查询，从主库查询数据。
        
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $channelType = Yii::$app->request->get('channelType') ? Yii::$app->request->get('channelType') : 1;//1是emarsys和emarsys_new通道，2是cheet通道
        
        if ($channelType == 1) {
            $channelWhere = ' channel_id in(2,6) ';
            $channelName = 'emarsys';
        } else if($channelType == 2) {
            $channelWhere = " channel_id = 4 ";
            $channelName = "cheet";
        } elseif($channelType == 5) {
            $channelWhere = " channel_id = 5 ";
            $channelName = "benchmark";
        } elseif($channelType == 9) {
            $channelWhere = " channel_id = 9 ";
            $channelName = "webpower";
        }
        $limit = 2000;
        $tableName = "mail_statistics_temp";
        
        //从明细表查询未被统计的数据
        $sql = "select site_id from {$tableName} where {$channelWhere} group by site_id limit 1";
        $siteIdOne = Yii::$app->db->createCommand($sql)->queryOne();
        
        
        if($siteIdOne) {
            $siteId = $siteIdOne['site_id'];
            $idsArr = $clickEmailsArr = $bouncesIdsArr = $successIdsArr = $inClickEmailArr = $hardBouncesIdsArr = $complaintIdsArr = array();
            
            $getDataSql = "select * from {$tableName} where {$channelWhere} and site_id = $siteId limit $limit";
            
            $allDataArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
            
            if($allDataArr) {
                
                $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$siteId]; 
                
                foreach($allDataArr as $allData) {
                    
                    $idsArr[] = $allData['id'];
                    
                    if ($allData['is_hard_bounces'] == 1) {
                        $hardBouncesIdsArr[] = $allData['id'];
                    }
                    if ($allData['is_complaint'] == 1) {
                        $complaintIdsArr[] =  $allData['id'];
                    }
                    if($allData['is_hard_bounces'] == 1 || $allData['is_soft_bounces'] == 1) {
                        $bouncesIdsArr[] = $allData['id'];
                    }
                    if($allData['is_send_success'] == 1) {
                        $successIdsArr[] = $allData['id'];
                    }
                    if($allData['is_click'] == 1) {
                        $clickEmailsArr[$allData['email']] = \yii::$app->bjTime->strtotime($allData['click_time']);
                        $inClickEmailArr[] = "'".$allData['email']."'";
                    }
                    
                }
                // 更新user表中相关用户硬弹状态赋值成1
                if ($hardBouncesIdsArr) {
                    $updateHardBouncesSql = "update user_{$curSiteName} u join {$tableName} t on u.email=t.email set u.is_bound=1 where t.id in(".join(',',$hardBouncesIdsArr).")";
                    $listData = array();
                    $listData['businessType'] = 1;//操作user表的业务类型 第三方通道
                    $listData['operateType'] = 2;//sql操作类型 2修改，1添加
                    $listData['site'] = strtolower($curSiteName);//站点名称
                    $listData['create_time'] = time();//添加时间
                    $listData['error_times'] = 0;//sql语句执行错误次数
                    $listData['sql'] = $updateHardBouncesSql;
                    $this->_lpushRedisList($curSiteName,$listData);
                   // Yii::$app->db->createCommand($updateHardBouncesSql)->execute();
                }
                
                // 更新user表中相关用户投诉状态赋值成1
                if ($complaintIdsArr) {
                    $updateComplaintSql = "update user_{$curSiteName} u join {$tableName} t on u.email=t.email set u.is_complaint=1 where t.id in(".join(',',$complaintIdsArr).")";
                    $listData = array();
                    $listData['businessType'] = 1;
                    $listData['operateType'] = 2;
                    $listData['site'] = strtolower($curSiteName);
                    $listData['create_time'] = time();
                    $listData['error_times'] = 0;
                    $listData['sql'] = $updateComplaintSql;
                    $this->_lpushRedisList($curSiteName,$listData);
                    //Yii::$app->db->createCommand($updateComplaintSql)->execute();
                }
                
                if($bouncesIdsArr) {
                    
                    // 把通道返回的失败数据写入到email_站点_send_success_fail_data
                    $insertSql = "insert into email_{$curSiteName}_send_success_fail_data(group_id,email,channel_id,status) select group_id,email,channel_id,0 from {$tableName} where id in(".join(',',$bouncesIdsArr).") ON DUPLICATE KEY UPDATE status=values(status)";
                    // 入库
                    Yii::$app->db->createCommand($insertSql)->execute();
                    
                    
                    $updateBouncesSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.group_id = t.group_id and m.email = t.email set m.send_status=2 where t.id in(".join(',',$bouncesIdsArr).")";
                    
                    Yii::$app->db->createCommand($updateBouncesSql)->execute();
                }
                
                if($successIdsArr) {
                    
                    // 把通道返回的成功数据写入到email_站点_send_success_fail_data
                    $insertSql = "insert into email_{$curSiteName}_send_success_fail_data(group_id,email,channel_id,status) select group_id,email,channel_id,1 from {$tableName} where id in(".join(',',$successIdsArr).") ON DUPLICATE KEY UPDATE status=values(status)";
                    // 入库
                    Yii::$app->db->createCommand($insertSql)->execute();
                    
                    $updateSuccessSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.group_id = t.group_id and m.email = t.email set m.send_status=1 where t.id in(".join(',',$successIdsArr).")";
                    
                    Yii::$app->db->createCommand($updateSuccessSql)->execute();
                }
                
                if($clickEmailsArr) {
                    $updateClickArr = $usersArr = $insertClickArr = array();
                    $updateClickSql = "update user_{$curSiteName} set last_click_time = CASE email ";
                    $clickEmailsStr = join(',', array_unique($inClickEmailArr));
                    foreach($clickEmailsArr as $email => $clickTime) {
                        
                        $updateClickArr[] = "WHEN '{$email}' THEN ".$clickTime." ";
                    }
                    if($updateClickArr) {
                        $updateClickSql .= join('',$updateClickArr)." END where email in (".$clickEmailsStr.")";

                        $listData = array();
                        $listData['businessType'] = 1;
                        $listData['operateType'] = 2;
                        $listData['site'] = strtolower($curSiteName);
                        $listData['create_time'] = time();
                        $listData['error_times'] = 0;
                        $listData['sql'] = $updateClickSql;
                        $this->_lpushRedisList($curSiteName,$listData);
                        // 入库
                        //Yii::$app->db->createCommand($updateClickSql)->execute();
                    }
                    
                    $selectClicksql = "select user_id,email from user_{$curSiteName} where email in (".$clickEmailsStr.")";
                    
                    $userData = Yii::$app->db->createCommand($selectClicksql)->queryAll();
                    
                    if($userData) {
                        foreach($userData as $user) {
                            $usersArr[$user['email']] = $user['user_id'];
                        }
                        if($usersArr) {
                            $insertClickSql = "insert into user_{$curSiteName}_email_click_record(user_id,email_click_time) values ";
                            foreach ($usersArr as $email => $userId) {
                                if(isset($clickEmailsArr[$email])) {
                                    $insertClickArr[] = "($userId,{$clickEmailsArr[$email]})";
                                }
                                
                            }
                            
                            if($insertClickArr) {
                                $insertClickSql .= join(',',$insertClickArr);
                                // 入库
                                Yii::$app->db->createCommand($insertClickSql)->execute();
                            }
                        }
                        
                    }
                    
                    
                }
                
                //处理完成后将其删除
                if($idsArr) {
                    $deleteSql = "delete from {$tableName} where id in(".join(',',$idsArr).")";
                    // 入库
                    Yii::$app->db->createCommand($deleteSql)->execute();
                }
            }
            
        }
        
    }
    
    /**
     * 数据统计
     */
    public function statisticsData($channelIdZdy=0) 
    {
        $tableName = "mail_trace_detail";
        $numbers = 3000;// 每次查询的数量
        
        if (empty($channelIdZdy)) {
            $channelWhere = ' (channel_id = 2 or channel_id = 6)';
            $redirectBaseUrl = "crontab/deal-channel-statistics-data/statistics-emarsys-data?site=marketing_email";
            
        } else {
            $channelWhere = " channel_id = {$channelIdZdy} ";
            $redirectBaseUrl = "crontab/deal-channel-statistics-data/statistics-cheet-data?site=marketing_email";
        }
//       $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/' ;
       
       for($i=0;$i<10;$i++) {
       
        // 查询相关数据
        $sql = "select * from {$tableName} where is_deal=0 and {$channelWhere} limit {$numbers}";
        $allEmailInfoArr = Yii::$app->db->createCommand($sql)->queryAll();
        //没有要处理的数据直接退出
        if($allEmailInfoArr){
        
                $curBjTime = \yii::$app->bjTime->time();
            
                // 遍历数据获取所有数据的分组名称，邮件类型,网站id
                $gidArr = [];
                $gidStr = "";
                
                $groupInfoArr = [];
                $groupInfoArrDeal = [];
                
                // 保存邮箱的相关信息
                $emailByGidArr = [];
                $emailByGidArrDeal = [];
                
                $idAllArr = $successIdAllArr = $bouncesIdAllArr = $complaintIdAllArr = $unsubscribeIdAllArr = $clickIdAllArr = []; // 所有要处理过id集合数组
                $idAllStr = $successIdAllStr = $bouncesIdAllStr = $complaintIdAllStr = $unsubscribeIdAllStr = $clickIdAllStr = ""; // 所有要处理过id集合字符串
                
                
                foreach ($allEmailInfoArr as $allEmailInfoArrK => $allEmailInfoArrV) {
                    if (!in_array($allEmailInfoArrV['gid'],$gidArr)) {
                        $gidArr[] = $allEmailInfoArrV['gid'];
                    }
                    $emailByGidArr[$allEmailInfoArrV['gid']][] = $allEmailInfoArrV['email'];
                    $idAllArr[] = $allEmailInfoArrV['id'];
                }
                $idAllStr = join(',',$idAllArr); // 所有要处理过id集合字符串
                /** 获取查询数据中所有分组的网站id和分组名称 start **/
                if ($gidArr) {
                    $gidStr = join(',',$gidArr);
                    // 从分组表中查询分组名称和网站id的相关数据
                    $sqlGroup = "select mg.group_id,mg.channel_id,mg.group_name,mg.site_id,mt.type_id from mail_group as mg left join mail_template as mt on mg.template_pk=mt.id where group_id in ({$gidStr})";
                    $groupInfoArr = Yii::$app->db->createCommand($sqlGroup)->queryAll();
                    if ($groupInfoArr) {
                        foreach ($groupInfoArr as $groupInfoArrV) {
                            $groupInfoArrDeal[$groupInfoArrV['group_id']] = $groupInfoArrV; 
                        }
                    }
                }
              
                /** 获取查询数据中所有分组的网站id和分组名称 end **/
                
                /** 获取每一个邮箱所属的邮件类型start **/
                foreach ($emailByGidArr as $emailByGidArrK => $emailByGidArrV) {
                    
                    if(isset($groupInfoArrDeal[$emailByGidArrK]['site_id'])){
                        $emailTempStr = join("','",$emailByGidArrV);
                        // 查询邮件类型数据
                        $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoArrDeal[$emailByGidArrK]['site_id']]; 
                        $tableNameSendList = "mail_sendlist_".$curSiteName;
                        $emailTypeSql = "select email,email_type from {$tableNameSendList} where email in ('{$emailTempStr}')";

                        $emailByGidArrDealTemp = Yii::$app->db->createCommand($emailTypeSql)->queryAll();

                        foreach ($emailByGidArrDealTemp as $emailByGidArrDealTempV) {
                            $emailByGidArrDeal[$emailByGidArrK][$emailByGidArrDealTempV['email']] = $emailByGidArrDealTempV;
                        }
                    }
                }
                /** 获取每一个邮箱所属的邮件类型end **/
               
                // 定义数量的保存数组 （某一分组,某一天,某一邮件类型，某一网站，某一通道）
                $emailClassifyNums = [];
                
                // 定义弹回的邮箱就是发送失败的邮箱数组
                $emailBouncesArr = [];
                // 定义点击的邮箱
                $emailClickArr = [];
                
                // 定义发送成功的邮箱数据
                $emailSuccessArr = [];
                $emailClickArrUnDeal = [];
                foreach ($allEmailInfoArr as $allEmailInfoArrK => $allEmailInfoArrV) {
                    
                    // 获取当前邮箱的邮件类型
                    if (isset($emailByGidArrDeal[$allEmailInfoArrV['gid']][$allEmailInfoArrV['email']])) {
                        $curEmailType =  $emailByGidArrDeal[$allEmailInfoArrV['gid']][$allEmailInfoArrV['email']]['email_type'];
                    } else {
                        continue;
                    }
                    
                    
                    if ($allEmailInfoArrV['is_send_success'] == 1 && $allEmailInfoArrV['is_send_success_deal'] == 0) { // 当前数据是否发送成功 （cheet通道才有相关数据）
                        $emailSuccessArr[$allEmailInfoArrV['gid']][] = $allEmailInfoArrV['email'];
                        $successIdAllArr[] = $allEmailInfoArrV['id'];
                        $emailSuccessTimeArr = explode(' ',$allEmailInfoArrV['send_success_time']);
                        if (isset($emailSuccessTimeArr[0])) {
                            $emailSuccessTimeDate = $emailSuccessTimeArr[0];
                        }
                        if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$emailSuccessTimeDate][$curEmailType]['send_success_nums'])) {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$emailSuccessTimeDate][$curEmailType]['send_success_nums']++;
                        } else {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$emailSuccessTimeDate][$curEmailType]['send_success_nums']= 1;
                        } 
                    } 
                    
                    if ($allEmailInfoArrV['is_hard_bounces'] == 1 && $allEmailInfoArrV['is_bounces_deal'] == 0) { // 当前数据是硬弹数据
                        $emailBouncesArr[$allEmailInfoArrV['gid']][] = $allEmailInfoArrV['email'];
                        $bouncesIdAllArr[] = $allEmailInfoArrV['id'];
                        $hardBouncesArr = explode(' ',$allEmailInfoArrV['bounces_time']);
                        if (isset($hardBouncesArr[0])) {
                            $hardBouncesDate = $hardBouncesArr[0];
                        }
                        if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$hardBouncesDate][$curEmailType]['hard_bounces_nums'])) {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$hardBouncesDate][$curEmailType]['hard_bounces_nums']++;
                        } else {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$hardBouncesDate][$curEmailType]['hard_bounces_nums']= 1;
                        } 
                    }
                    
                    if ($allEmailInfoArrV['is_soft_bounces'] == 1 && $allEmailInfoArrV['is_bounces_deal'] == 0) { // 当前数据是软弹
                        $emailBouncesArr[$allEmailInfoArrV['gid']][] = $allEmailInfoArrV['email'];
                        $bouncesIdAllArr[] = $allEmailInfoArrV['id'];
                        $softBouncesArr = explode(' ',$allEmailInfoArrV['bounces_time']);
                        if (isset($softBouncesArr[0])) {
                            $softBouncesDate = $softBouncesArr[0];
                        }
                        if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$softBouncesDate][$curEmailType]['soft_bounces_nums'])) {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$softBouncesDate][$curEmailType]['soft_bounces_nums']++;
                        } else {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$softBouncesDate][$curEmailType]['soft_bounces_nums']= 1;
                        }
                        
                    }
                    
                    
                    
                    if ($allEmailInfoArrV['is_complaint'] == 1 && $allEmailInfoArrV['is_complaint_deal'] == 0) { // 当前数据是投诉
                        $complaintIdAllArr[] = $allEmailInfoArrV['id'];
                        $complaintArr = explode(' ',$allEmailInfoArrV['complaint_time']);
                        if (isset($complaintArr[0])) {
                            $complaintDate = $complaintArr[0];
                        }
                        if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$complaintDate][$curEmailType]['complaint_nums'])) {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$complaintDate][$curEmailType]['complaint_nums']++;
                        } else {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$complaintDate][$curEmailType]['complaint_nums']= 1;
                        }
                        
                    }
                    
                    if ($allEmailInfoArrV['is_unsubscribe'] == 1 && $allEmailInfoArrV['is_unsubscribe_deal'] == 0) { // 当前数据是退订
                        $unsubscribeIdAllArr[] = $allEmailInfoArrV['id'];
                        $unsubscribeArr = explode(' ',$allEmailInfoArrV['unsubscribe_time']);
                        if (isset($unsubscribeArr[0])) {
                            $unsubscribeDate = $unsubscribeArr[0];
                        }
                        if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$unsubscribeDate][$curEmailType]['unsubscribe_nums'])) {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$unsubscribeDate][$curEmailType]['unsubscribe_nums']++;
                        } else {
                            $emailClassifyNums[$allEmailInfoArrV['gid']][$unsubscribeDate][$curEmailType]['unsubscribe_nums']= 1;
                        }
                        
                    }
                    
                    if ($allEmailInfoArrV['is_click'] == 1) { // 当前数据是点击
                        
                        $clickDateArr = explode('.',$allEmailInfoArrV['click_time']);
                        if (isset($clickDateArr[0])) {
                            $clickTime = \yii::$app->bjTime->strtotime($clickDateArr[0]);
                        } else {
                            $clickTime = 0;
                        }
                        $emailClickArrUnDeal[$allEmailInfoArrV['gid']][] = $allEmailInfoArrV['email'];
                        
                        $emailClickArr[$allEmailInfoArrV['gid']][$allEmailInfoArrV['email']]['email_click_time'] = $clickTime;
                        
                        if($allEmailInfoArrV['is_click_deal'] == 0){//如果点击数没有统计才进行统计
                            $clickIdAllArr[] = $allEmailInfoArrV['id'];
                            $clickArr = explode(' ',$allEmailInfoArrV['click_time']);
                            if (isset($clickArr[0])) {
                                $clickDate = $clickArr[0];
                            }
                            if (isset($emailClassifyNums[$allEmailInfoArrV['gid']][$clickDate][$curEmailType]['click_nums'])) {
                                $emailClassifyNums[$allEmailInfoArrV['gid']][$clickDate][$curEmailType]['click_nums']++;
                            } else {
                                $emailClassifyNums[$allEmailInfoArrV['gid']][$clickDate][$curEmailType]['click_nums']= 1;
                            } 
                        }
                    }
                    
                }
                
                // 处理点击数据
                if ($emailClickArr) {
                    $emailClickStrUnDeal = '';
                    foreach ($emailClickArr as $emailClickArrK=>$emailClickArrV) {
                        // 把点击数据插入相关表的sql
                        $emailClickStrUnDeal = join("','",$emailClickArrUnDeal[$emailClickArrK]);
                        $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoArrDeal[$emailClickArrK]['site_id']]; 
                        $curTableName = 'user_'.$curSiteName;
                        
                        
                        $emailSelectSql = "select email,user_id from {$curTableName} where email in ('{$emailClickStrUnDeal}')";
                        $userInfo = Yii::$app->db->createCommand($emailSelectSql)->queryAll();
                        
                        $curClickTableName = "user_{$curSiteName}_email_click_record";
                        $insertClickSql = "insert into {$curClickTableName}(user_id,email_click_time) values";
                        $valueSql = "";
                        foreach ($userInfo as $userInfoV) { // 组装sql语句 
                            $valueSql .= '('.$userInfoV['user_id'].','.$emailClickArrV[$userInfoV['email']]['email_click_time'].'),';
                            
                            // 更新用户总表中的最后一次点击时间
                            $updateClickSql = "update {$curTableName} set last_click_time = {$emailClickArrV[$userInfoV['email']]['email_click_time']} where user_id = {$userInfoV['user_id']}";
                            Yii::$app->db->createCommand($updateClickSql)->execute();
                        }
                       
                        if ($valueSql) {
                           $valueSql = rtrim($valueSql,',');
                           Yii::$app->db->createCommand($insertClickSql.$valueSql)->execute();
                        }
                    }
                }
                
                // 发送失败数据更新到队列表中
                if ($emailBouncesArr) {
                    foreach ($emailBouncesArr as $emailBouncesArrK=>$emailBouncesArrV) {    
                        $emailBouncesStr = join("','",$emailBouncesArrV);
                        if ($emailBouncesStr) {
                            $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoArrDeal[$emailBouncesArrK]['site_id']]; 
                            $curEmailListTable = 'mail_sendlist_'.$curSiteName;
                            $updateEmailListSql = "update {$curEmailListTable} set send_status=2 where group_id={$emailBouncesArrK} and send_status=0 and email in ('{$emailBouncesStr}')";
                            Yii::$app->db->createCommand($updateEmailListSql)->execute(); 
                        }
                    }
                }
                
                // 发送成功数据更新到队列表中  只有cheet通道要进行相关的操作
                if ($channelIdZdy == 4) {
                    if ($emailSuccessArr) {
                        foreach ($emailSuccessArr as $emailSuccessArrK=>$emailSuccessArrV) {    
                            $emailSuccessStr = join("','",$emailSuccessArrV);
                            if ($emailSuccessStr) {
                                $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoArrDeal[$emailSuccessArrK]['site_id']]; 
                                $curEmailListTable = 'mail_sendlist_'.$curSiteName;
                                $updateEmailListSql = "update {$curEmailListTable} set send_status=1 where group_id={$emailSuccessArrK} and send_status=0 and email in ('{$emailSuccessStr}')";
                                Yii::$app->db->createCommand($updateEmailListSql)->execute(); 
                            }
                        }
                    }
                }
                
               
                // 统计数据入库
                foreach ($emailClassifyNums as $gid=>$emailClassifyNumsV) {
                    
                    // 根据gid获取相关的分组信息
                    $channelId = $groupInfoArrDeal[$gid]['channel_id'];
                    $groupName = $groupInfoArrDeal[$gid]['group_name'];
                    $siteId = $groupInfoArrDeal[$gid]['site_id'];
                    $templateTypeId = $groupInfoArrDeal[$gid]['type_id'];
                    foreach ($emailClassifyNumsV as $statDate=>$emailClassifyNumsVV) {
                        foreach ($emailClassifyNumsVV as $emailType=>$emailClassifyNumsVVV) {
                            
                            $selectSql = "select count(*) from channel_return_info_statistics where gid={$gid} and stat_date='{$statDate}' and email_type={$emailType}";
                            $gidDateType = Yii::$app->db->createCommand($selectSql)->queryScalar();
                            
                            if ($gidDateType) { // 更新数据
                                if (empty($emailClassifyNumsVVV['hard_bounces_nums'])) {
                                    $emailClassifyNumsVVV['hard_bounces_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['soft_bounces_nums'])) {
                                    $emailClassifyNumsVVV['soft_bounces_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['complaint_nums'])) {
                                    $emailClassifyNumsVVV['complaint_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['unsubscribe_nums'])) {
                                    $emailClassifyNumsVVV['unsubscribe_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['click_nums'])) {
                                    $emailClassifyNumsVVV['click_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['send_success_nums'])) {
                                    $emailClassifyNumsVVV['send_success_nums'] = 0;
                                }
                                $updateSql = "update channel_return_info_statistics set hard_bounces_nums = hard_bounces_nums+{$emailClassifyNumsVVV['hard_bounces_nums']},soft_bounces_nums=soft_bounces_nums+{$emailClassifyNumsVVV['soft_bounces_nums']},complaint_nums = complaint_nums+{$emailClassifyNumsVVV['complaint_nums']},unsubscribe_nums = unsubscribe_nums+{$emailClassifyNumsVVV['unsubscribe_nums']}, click_nums = click_nums+{$emailClassifyNumsVVV['click_nums']},send_success_nums = send_success_nums+{$emailClassifyNumsVVV['send_success_nums']},stat_time={$curBjTime} where gid={$gid} and stat_date='{$statDate}' and email_type={$emailType}";
                              
                                Yii::$app->db->createCommand($updateSql)->execute();    
                            } else {  // 插入数据
                            
                                if (empty($emailClassifyNumsVVV['hard_bounces_nums'])) {
                                    $emailClassifyNumsVVV['hard_bounces_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['soft_bounces_nums'])) {
                                    $emailClassifyNumsVVV['soft_bounces_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['complaint_nums'])) {
                                    $emailClassifyNumsVVV['complaint_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['unsubscribe_nums'])) {
                                    $emailClassifyNumsVVV['unsubscribe_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['click_nums'])) {
                                    $emailClassifyNumsVVV['click_nums'] = 0;
                                }
                                
                                if (empty($emailClassifyNumsVVV['send_success_nums'])) {
                                    $emailClassifyNumsVVV['send_success_nums'] = 0;
                                }
                                
                                $insertSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,hard_bounces_nums,soft_bounces_nums,complaint_nums,unsubscribe_nums,send_success_nums,click_nums,stat_date,stat_time) values ({$channelId},{$siteId},{$gid},'{$groupName}',{$emailType},{$templateTypeId},{$emailClassifyNumsVVV['hard_bounces_nums']},{$emailClassifyNumsVVV['soft_bounces_nums']},{$emailClassifyNumsVVV['complaint_nums']},{$emailClassifyNumsVVV['unsubscribe_nums']},{$emailClassifyNumsVVV['send_success_nums']},{$emailClassifyNumsVVV['click_nums']},'{$statDate}',{$curBjTime})";
                               
                                
                                Yii::$app->db->createCommand($insertSql)->execute();   
                            }
                        }
                    }
                }
                // 更新处理的数据把处理过的记录标记上
                if ($idAllStr) {
                    $updateDealState = "update {$tableName} set is_deal=1 where id in ({$idAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                if ($successIdAllArr) {
                    $successIdAllStr = join(',',$successIdAllArr);
                    $updateDealState = "update {$tableName} set is_send_success_deal=1 where id in ({$successIdAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                if ($bouncesIdAllArr) {
                    $bouncesIdAllStr = join(',',$bouncesIdAllArr);
                    $updateDealState = "update {$tableName} set is_bounces_deal=1 where id in ({$bouncesIdAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                if ($complaintIdAllArr) {
                    $complaintIdAllStr = join(',',$complaintIdAllArr);
                    $updateDealState = "update {$tableName} set is_complaint_deal=1 where id in ({$complaintIdAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                if ($unsubscribeIdAllArr) {
                    $unsubscribeIdAllStr = join(',',$unsubscribeIdAllArr);
                    $updateDealState = "update {$tableName} set is_unsubscribe_deal=1 where id in ({$unsubscribeIdAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                if ($clickIdAllArr) {
                    $clickIdAllStr = join(',',$clickIdAllArr);
                    $updateDealState = "update {$tableName} set is_click_deal=1 where id in ({$clickIdAllStr})";
                    Yii::$app->db->createCommand($updateDealState)->execute();
                }
                
            } else {
                break;
            }
       
        }
         
    }
    
    /*
     * 记录相关日志
     */
    private function writeLog($msg)
    {
        $date = yii::$app->bjTime->date("Y-m-d H:i:s");
        $today = yii::$app->bjTime->date("Ymd");
        $dir = yii::$app->getRuntimePath() . "/logs/" . yii::$app->bjTime->date("Ym");
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $filePath = $dir . "/do_channal_data_{$today}.log";
        $msg = "[{$date}] $msg" . PHP_EOL;
        file_put_contents($filePath, $msg, FILE_APPEND);
    }

    /**
     * TODO:将数据插入到redis list 表中
     * @param $listData
     * @param $type
     * @author: eaglezhao
     * @date:2018-02-21
     */
    private function _lpushRedisList($site,$listData)
    {
        $key = Yii::$app->params['userInfoListKey'].":".strtolower($site);
        $redis = $this->redis;
        $returnFlag = $redis->lpush($key,json_encode($listData));
        return $returnFlag;

    }
    
}